import os
import re
import time

import pymysql.cursors

# 连接数据库
connect = pymysql.Connect(
    host='172.27.1.101',
    port=8066,
    user='test',
    passwd='test',
    db='TESTDB',
    charset='utf8'
)
# 要生成的表
tables = ['trainplan_user']

# 生成文件的路径
file_path = r'C:\Users\zhangqiang\Desktop\datax\mysql_json\\'


# 创建文件
# file_path：文件路径
# msg：即要写入的内容
def create__file(file_path, msg):
    f = open(file_path, "a")
    f.write(msg)
    f.close


def mkdir(path):
    # 去除首位空格
    path = path.strip()
    # 去除尾部 \ 符号
    path = path.rstrip("\\")

    # 判断路径是否存在
    # 存在     True
    # 不存在   False
    isExists = os.path.exists(path)

    # 判断结果
    if not isExists:
        # 如果不存在则创建目录
        # 创建目录操作函数
        os.makedirs(path)

        print(path + ' 创建成功')
        return True
    else:
        # 如果目录存在则不创建，并提示目录已存在
        print(path + ' 目录已存在')
        return False


def data_type(type):
    dType = {'bigint': 'BIGINT',
             'integer': 'BIGINT',
             'int': 'BIGINT',
             'tinyint': 'BIGINT',
             'smallint': 'BIGINT',
             'float': 'DOUBLE',
             'double': 'DOUBLE',
             'decimal': 'DOUBLE',
             'binary': 'BINARY',
             'varbinary': 'BINARY',
             'char': 'STRING',
             'varchar': 'STRING',
             'mediumtext': 'STRING',
             'text': 'STRING',
             'datetime': 'STRING',
             'time': 'STRING',
             'timestamp': 'STRING',
             'bit': 'BOOLEAN',
             'date': 'date',
             'json': 'MAP<STRING,STRING>'}
    dicStr = [dType[i] for i in [type]]
    return dicStr[0]


# 获取游标
cursor = connect.cursor()

# json_str定义
json_str1 = '{\n\t"job": {\n\t\t"setting": {\n\t\t\t"speed": {\n\t\t\t\t"channel": 10\n\t\t\t\t}\n\t\t\t},\n\t\t"content":' \
            '[\n\t\t\t{\n\t\t\t\t"reader": {\n\t\t\t\t\t"name": "mysqlreader",\n\t\t\t\t\t"parameter": {\n\t\t\t\t\t\t"username": ' \
            '"test",\n\t\t\t\t\t\t"password": "test",\n\t\t\t\t\t\t"connection": [\n\t\t\t\t\t\t\t{\n\t\t\t\t\t\t\t\t"querySql": ['''
json_str2 = '\n\t\t\t\t\t\t\t\t],\n\t\t\t\t\t\t\t\t"jdbcUrl": [' \
            '\n\t\t\t\t\t\t\t\t\t"jdbc:mysql://mycat1:8060/TESTDB?useUnicode=true&characterEncoding=UTF-8"\n\t\t\t\t' \
            '\t\t\t\t]\n\t\t\t\t\t\t\t}\n\t\t\t\t\t\t]\n\t\t\t\t\t}\n\t\t\t\t}, '
json_str3 = '\n\t\t\t\t"writer":{\n\t\t\t\t\t"name": "hdfswriter",\n\t\t\t\t\t"parameter":{\n\t\t\t\t\t\t"defaultFS": ' \
            '"hdfs://master1:8020",\n\t\t\t\t\t\t"fileType": "text",\n\t\t\t\t\t\t"path": ' \
            '"/user/hive/warehouse/data_analysis.db/ods_data_analysis_'
json_str4 = '",\n\t\t\t\t\t\t"fileName": "ods_data_analysis_'
json_str5 = '",\n\t\t\t\t\t\t"column": ['
json_str6 = '\n\t\t\t\t\t\t],\n\t\t\t\t\t\t"writeMode": "append",\n\t\t\t\t\t\t"fieldDelimiter": "\\t",' \
            '\n\t\t\t\t\t\t"compress":"GZIP"\n\t\t\t\t\t}\n\t\t\t\t}\n\t\t\t}\n\t\t]\n\t}\n} '

for i in tables:
    # sql_str定义
    sql_str = '"select '
    sql_h1 = 'CREATE EXTERNAL TABLE data_analysis.ods_data_analysis_' + i + '('
    sql_h2 = '\n)\n ROW FORMAT DELIMITED FIELDS TERMINATED BY \'\\t\' \n STORED AS TEXTFILE \nLOCATION ' \
             '\'/user/hive/warehouse/data_analysis.db/ods_data_analysis_' + i + '\'; '
    datax_str = ''
    col_str = ''

    # 查询数据
    sql = "show full columns from %s;" % i
    cursor.execute(sql)
    columns = cursor.fetchall()

    # 获取mysql_table字段和类型
    data = [
        {
            'name': i[0],
            'type': i[1]
        } for i in columns
    ]

    # sql_str组装
    for j in data:
        tc = '\n\t\t\t\t\t\t\t{'
        list1 = [j[k] for k in ['name', 'type']]
        column = list1[0]
        type = data_type(list1[1].split('(')[0])
        # sql语句拼接
        sql_str += column + ','
        # 表字段
        tc += '\n\t\t\t\t\t\t\t\t"name": "' + column + '",' \
                                                       '\n\t\t\t\t\t\t\t\t"type": "' + type + \
              '"\n\t\t\t\t\t\t\t},'
        col_str += tc
        # hive表创建语句
        sql_h1 += '\n' + column + ' ' + type + ','

    sql_str = sql_str[:-1] + ' from ' + i + ';"'
    col_str = col_str[:-1]
    sql_h1 = sql_h1[:-1] + sql_h2;
    datax_str = json_str1 + '\n\t\t\t\t\t\t\t\t\t' + sql_str + json_str2 + json_str3 + i + json_str4 + i + json_str5 + col_str + json_str6;
    # 定义要创建的目录
    mkpath = file_path + re.sub('_', '', i)
    # 调用函数
    if (mkdir(mkpath)):
        create__file(mkpath + '\\' + re.sub('_', '', i) + '2hive.json', datax_str)
        create__file(mkpath + '\\' + re.sub('_', '', i) + '2hive.sql', sql_h1)
        print('success')
        time.sleep(1)
    else:
        print('exists')
    print('共查找出', cursor.rowcount, '条数据')
# 关闭连接
cursor.close()
connect.close()
